set hive.exec.mode.local.auto = false;
-- 流量类指标_平台统计表 ads_nshop_platform_flow_stat
insert overwrite table ads_nshop.ads_nshop_platform_flow_stat partition (bdp_day = '20220510')
select b.customer_gender,
       b.customer_age_range,
       b.customer_natives,
       sum(a.view_count),
       sum(a.view_count) / count(a.user_id)
from dws_nshop.dws_nshop_ulog_view a
         join ods_nshop.ods_02_customer b
              on a.user_id = b.customer_id
where bdp_day = '20220510'
group by b.customer_gender,
         b.customer_age_range,
         b.customer_natives;

-- 流量类指标_平台流量统计P/UV ads_nshop_flowpu_stat
insert overwrite table ads_nshop.ads_nshop_flowpu_stat partition (bdp_day = '20220510')
select count(device_num),
       sum(view_count),
       sum(view_count) / count(user_id)
from dws_nshop.dws_nshop_ulog_view
where bdp_day = '20220510';

-- 平台搜索热词统计 ads_nshop_search_keys
insert overwrite table ads_nshop.ads_nshop_search_keys partition (bdp_day = '20220510')
select a.target_keys,
       b.customer_gender,
       b.customer_age_range,
       a.os,
       a.manufacturer,
       a.area_code,
       count(a.user_id),
       count(a.target_keys),
       a.target_order,
       count(a.target_id)
from dwd_nshop.dwd_nshop_actlog_pdtsearch a
         join ods_nshop.ods_02_customer b on a.user_id = b.customer_id
group by b.customer_gender,
         b.customer_age_range,
         a.os,
         a.manufacturer,
         a.area_code,
         a.target_order,
         a.target_keys;

-- 用户类启动情况表
insert overwrite table ads_nshop.ads_nshop_actlog_launch_gsets partition (bdp_day = '20220510')
select os,
       manufacturer,
       carrier,
       network_type,
       area_code,
       count(user_id),
       sum(launch_count)
from dws_nshop.dws_nshop_ulog_launch
where bdp_day = '20220510'
group by os, manufacturer, carrier, network_type, area_code;
insert overwrite table ads_nshop.ads_nshop_actlog_launch_rollup partition (bdp_day = '20220510')
select os,
       manufacturer,
       carrier,
       network_type,
       area_code,
       count(user_id),
       sum(launch_count)
from dws_nshop.dws_nshop_ulog_launch
where bdp_day = '20220510'
group by os, manufacturer, carrier, network_type, area_code
with rollup;

-- 用户类指标统计表 ads_nshop_customer_stat
with tmp_launch as (
    select *
    from dws_nshop.dws_nshop_ulog_launch
    where bdp_day = '20220510'
)
insert overwrite table ads_nshop.ads_nshop_customer_stat
partition(bdp_day = '20220510')
select a.customer_gender,
       a.customer_age_range,
       a.customer_natives,
       count(distinct a.customer_id),
       count(`if`(from_unixtime(cast(a.customer_ctime / 1000 as int), 'yyyyMMdd') = '20191101', 1, null)),
       count(distinct b.user_id)
from ods_nshop.ods_02_customer a
         left join tmp_launch b
                   on a.customer_device_num = b.device_num
group by a.customer_gender, a.customer_age_range, a.customer_natives;

-- 总体运营指标统计表 ads_nshop_oper_stat
with t_order as (
    select c.customer_gender,
           c.customer_age_range,
           c.customer_natives,
           b.category_code,
           count(distinct order_id) count_order,
           sum(a.payment_money) total_order,
           sum(a.district_money) total_district,
           sum(a.shipping_money) total_ship,
           sum(a.payment_money) / count(distinct order_id) per_order
    from dwd_nshop.dwd_nshop_orders_details a
             join ods_nshop.dim_pub_product b
                  on a.product_code = b.product_code
             join ods_nshop.ods_02_customer c
                  on a.customer_id = c.customer_id
    where a.bdp_day = '20220510'
    group by c.customer_gender, c.customer_age_range, c.customer_natives, b.category_code
), t_view as (
    select b.customer_gender,
           b.customer_age_range,
           b.customer_natives,
           d.category_code,
           count(target_id) count_view
    from dwd_nshop.dwd_nshop_actlog_pdtview a
    join ods_nshop.ods_02_customer b
    on a.user_id = b.customer_id
    join ods_nshop.dim_pub_page c
    on a.target_id = c.page_code and c.page_type = '4'
    join ods_nshop.dim_pub_product d
    on c.page_target = d.product_code
    where a.bdp_day = '20220510'
    group by b.customer_gender, b.customer_age_range, b.customer_natives, d.category_code
)
insert overwrite table ads_nshop.ads_nshop_oper_stat partition (bdp_day = '20220510')
select a.customer_gender,
       a.customer_age_range,
       c.province_name,
       a.category_code,
       a.count_order,
       `if`(b.count_view is null , 1, a.count_order / b.count_view),
       a.total_order,
       a.total_district,
       a.total_ship,
       a.per_order
from t_order a left join t_view b
    on a.customer_gender = b.customer_gender
       and a.customer_age_range = b.customer_age_range
       and a.customer_natives = b.customer_natives
       and a.category_code = b.category_code
    join ods_nshop.ods_01_base_area c
    on a.customer_natives = c.area_code;

-- 风控类指标统计表
insert overwrite table ads_nshop.ads_nshop_risk_mgt partition (bdp_day = "20220510")
select c.customer_gender,
       c.customer_age_range,
       d.province_name,
       b.category_code,
       count(`if`(a.order_status='6', 1, null)),
       count(`if`(a.order_status='6', 1, null)) / count(order_id) *100
from dwd_nshop.dwd_nshop_orders_details a
join ods_nshop.dim_pub_product b
    on a.product_code = b.product_code
join ods_nshop.ods_02_customer c
    on a.customer_id = c.customer_id
join ods_nshop.ods_01_base_area d
    on c.customer_natives = d.area_code
where a.bdp_day='20220510'
group by c.customer_gender, c.customer_age_range, d.province_name, b.category_code;
-- 支付类指标统计表
insert overwrite table ads_nshop.ads_nshop_pay_stat_gsets
    partition (bdp_day='20220510')
select b.customer_gender,
       b.customer_age_range,
       d.province_name,
       a.pay_type,
       a.pay_nettype,
       sum(a.payment_money),
       count(if(c.pay_status='1', 1, null)),
       count(if(c.pay_status='0', 1, null)),
       count(a.order_id),
       count(a.customer_id),
       sum(a.product_cnt),
       sum(if(c.pay_status='1', c.pay_amount, 0)) / sum(a.payment_money),
       count(if(c.pay_status='1', c.pay_id, null)) / count(a.order_id)
from dwd_nshop.dwd_nshop_orders_details a
join ods_nshop.ods_02_customer b
on a.customer_id = b.customer_id
join ods_nshop.ods_02_orders_pay_records c
on a.order_id = c.order_id
join ods_nshop.ods_01_base_area d
on b.customer_natives = d.area_code
where a.bdp_day = '20220510'
group by b.customer_gender, b.customer_age_range, d.province_name, a.pay_type, a.pay_nettype;

insert overwrite table ads_nshop.ads_nshop_pay_stat_rollup
    partition (bdp_day='20220510')
select b.customer_gender,
       b.customer_age_range,
       b.customer_natives,
       a.pay_type,
       a.pay_nettype,
       sum(a.payment_money),
       count(if(c.pay_status='1', 1, null)),
       count(if(c.pay_status='0', 1, null)),
       count(a.order_id),
       count(a.customer_id),
       sum(a.product_cnt),
       sum(if(c.pay_status='1', c.pay_amount, 0)) / sum(a.payment_money),
       count(if(c.pay_status='1', c.pay_id, null)) / count(a.order_id)
from dwd_nshop.dwd_nshop_orders_details a
         join ods_nshop.ods_02_customer b
              on a.customer_id = b.customer_id
         join ods_nshop.ods_02_orders_pay_records c
              on a.order_id = c.order_id
where a.bdp_day = '20220510'
group by b.customer_gender, b.customer_age_range, b.customer_natives, a.pay_type, a.pay_nettype with rollup;

-- 支付统计TopN，top100
insert overwrite table ads_nshop.ads_nshop_pay_stat_topn partition (bdp_day='20220510')
select case when pay_type='10' then '网上银行'
    when pay_type='11' then '微信'
    when pay_type='12' then '支付宝'
    else '线下支付' end,
       customer_natives,
       sum(pay_order_counts) total_order,
       sum(pay_amounts) total_amount
from ads_nshop.ads_nshop_pay_stat_gsets
where bdp_day = '20220510'
group by pay_type, customer_natives
order by total_order desc , total_amount desc
limit 500;

-- 交易类指标表
insert overwrite table ads_nshop.ads_nshop_busi_stat partition (bdp_day='20220510')
select b.customer_gender,
       b.customer_age_range,
       f.province_name,
       e.category_code,
       d.supplier_type,
       count(distinct `if`(a.order_status!='7', a.order_id, null)),
       sum(`if`(a.order_status!='7', a.payment_money, 0)),
       count(distinct `if`(a.order_status!='7', a.customer_id, null)),
       sum(distinct `if`(a.order_status!='7', a.product_cnt, null)),
       count(`if`(a.order_status='7', a.order_id, null)),
       sum(`if`(a.order_status='7', a.payment_money, 0))
from dwd_nshop.dwd_nshop_orders_details a
join ods_nshop.ods_02_customer b
on a.customer_id = b.customer_id
join ods_nshop.dim_pub_supplier d
on a.supplier_code = d.supplier_code
join ods_nshop.dim_pub_product e
on a.product_code = e.product_code
join ods_nshop.ods_01_base_area f
on b.customer_natives = f.area_code
where a.bdp_day = '20220510'
group by b.customer_gender, b.customer_age_range, f.province_name, e.category_code, d.supplier_type;
insert overwrite table ads_nshop.ads_nshop_busi_stat_rollup partition (bdp_day='20220510')
select b.customer_gender,
       b.customer_age_range,
       b.customer_natives,
       e.category_code,
       d.supplier_type,
       count(distinct `if`(a.order_status!='7', a.order_id, null)),
       sum(`if`(a.order_status!='7', a.payment_money, 0)),
       count(distinct `if`(a.order_status!='7', a.customer_id, null)),
       sum(distinct `if`(a.order_status!='7', a.product_cnt, null)),
       count(`if`(a.order_status='7', a.order_id, null)),
       sum(`if`(a.order_status='7', a.payment_money, 0))
from dwd_nshop.dwd_nshop_orders_details a
         join ods_nshop.ods_02_customer b
              on a.customer_id = b.customer_id
         join ods_nshop.dim_pub_supplier d
              on a.supplier_code = d.supplier_code
         join ods_nshop.dim_pub_product e
              on a.product_code = e.product_code
where a.bdp_day = '20220510'
group by b.customer_gender, b.customer_age_range, b.customer_natives, e.category_code, d.supplier_type with rollup;

insert overwrite table ads_nshop.ads_nshop_busi_stat_topn partition (bdp_day='20220510')
select customer_area_code,
       category_type,
       supplier_type,
       sum(busi_succ_orders) total_orders,
       sum(busi_succ_amounts) total_amounts,
       sum(busi_succ_users),
       sum(busi_succ_products),
       sum(busi_fail_orders),
       sum(busi_fail_amounts)
from ads_nshop.ads_nshop_busi_stat
where bdp_day = '20220510'
group by customer_area_code, category_type, supplier_type
order by total_orders desc, total_amounts desc
limit 100;

-- 广告投放类指标统计
insert overwrite table ads_nshop.ads_nshop_release_stat partition(bdp_day='20220510')
select
    a.device_type,
    a.os,
    b.customer_gender,
    b.customer_age_range,
    e.province_name,
    a.release_sources,
    a.release_category,
    count(distinct a.device_num),
    count(device_num)
from dwd_nshop.dwd_nshop_releasedatas a
         join ods_nshop.ods_02_customer b on a.customer_id=b.customer_id
        join ods_nshop.ods_01_base_area e on b.customer_natives = e.area_code
where a.bdp_day='20220510'
group by
    a.device_type,
    a.os,
    b.customer_gender,
    b.customer_age_range,
    e.province_name,
    a.release_sources,
    a.release_category;



insert overwrite table ads_nshop.ads_nshop_oper_stat2 partition(bdp_day='20220510')
select
    a.customer_gender,
    a.customer_age_range,
    e.province_name,
    c.category_code,
    count(distinct b.order_id) ,
    count(distinct b.order_id) / sum(d.view_count),
    sum(b.payment_money),
    sum(b.district_money),
    sum(b.shipping_money),
    sum(b.payment_money) / count(distinct b.customer_id)
from ods_nshop.ods_02_customer a
         join dwd_nshop.dwd_nshop_orders_details b
              on a.customer_id=b.customer_id
         join ods_nshop.dim_pub_product c
              on b.supplier_code=c.supplier_code
         join dws_nshop.dws_nshop_ulog_view d
              on b.customer_id=d.user_id
         join ods_nshop.ods_01_base_area e
              on a.customer_natives=e.area_code
where d.bdp_day='20220510'
group by
    a.customer_gender,
    a.customer_age_range,
    e.province_name,
    c.category_code;


